<?php
// +----------------------------------------------------------------------
// | ThinkPHP [ WE CAN DO IT JUST THINK IT ]
// +----------------------------------------------------------------------
// | Copyright (c) 2006-2012 http://thinkphp.cn All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: liu21st <liu21st@gmail.com>
// +----------------------------------------------------------------------
// $Id: DbPgsql.class.php 2741 2012-02-17 09:00:07Z liu21st $


/**
 +---------------------------
 * Pgsql数据库驱动类
 +---------------------------
 */
class DbPgsql extends Db {
	
	/**
     +----------------------------------------------------------
	 * 架构函数 读取数据库配置信息
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 * @param array $config 数据库配置数组
     +----------------------------------------------------------
	 */
	public function __construct($config = '') {
		if (! extension_loaded ( 'pgsql' )) {
			throw_exception ( L ( '_NOT_SUPPERT_' ) . ':pgsql' );
		}
		if (! empty ( $config )) {
			$this->config = $config;
			if (empty ( $this->config ['params'] )) {
				$this->config ['params'] = array ();
			}
		}
	}
	
	/**
     +----------------------------------------------------------
	 * 连接数据库方法
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 * @throws ThinkExecption
     +----------------------------------------------------------
	 */
	public function connect($config = '', $linkNum = 0) {
		if (! isset ( $this->linkID [$linkNum] )) {
			if (empty ( $config ))
				$config = $this->config;
			$pconnect = ! empty ( $config ['params'] ['persist'] ) ? $config ['params'] ['persist'] : $this->pconnect;
			$conn = $pconnect ? 'pg_pconnect' : 'pg_connect';
			$this->linkID [$linkNum] = $conn ( 'host=' . $config ['hostname'] . ' port=' . $config ['hostport'] . ' dbname=' . $config ['database'] . ' user=' . $config ['username'] . '  password=' . $config ['password'] );
			if (0 !== pg_connection_status ( $this->linkID [$linkNum] )) {
				throw_exception ( $this->error ( false ) );
			}
			//设置编码
			pg_set_client_encoding ( $this->linkID [$linkNum], C ( 'DB_CHARSET' ) );
			//$pgInfo = pg_version($this->linkID[$linkNum]);
			//$dbVersion = $pgInfo['server'];
			// 标记连接成功
			$this->connected = true;
			//注销数据库安全信息
			if (1 != C ( 'DB_DEPLOY_TYPE' ))
				unset ( $this->config );
		}
		return $this->linkID [$linkNum];
	}
	
	/**
     +----------------------------------------------------------
	 * 释放查询结果
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 */
	public function free() {
		pg_free_result ( $this->queryID );
		$this->queryID = null;
	}
	
	/**
     +----------------------------------------------------------
	 * 执行查询 返回数据集
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 * @param string $str  sql指令
     +----------------------------------------------------------
	 * @return mixed
     +----------------------------------------------------------
	 * @throws ThinkExecption
     +----------------------------------------------------------
	 */
	public function query($str) {
		$this->initConnect ( false );
		if (! $this->_linkID)
			return false;
		$this->queryStr = $str;
		//释放前次的查询结果
		if ($this->queryID)
			$this->free ();
		N ( 'db_query', 1 );
		// 记录开始执行时间
		G ( 'queryStartTime' );
		$this->queryID = pg_query ( $this->_linkID, $str );
		$this->debug ();
		if (false === $this->queryID) {
			$this->error ();
			return false;
		} else {
			$this->numRows = pg_num_rows ( $this->queryID );
			return $this->getAll ();
		}
	}
	
	/**
     +----------------------------------------------------------
	 * 执行语句
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 * @param string $str  sql指令
     +----------------------------------------------------------
	 * @return integer
     +----------------------------------------------------------
	 * @throws ThinkExecption
     +----------------------------------------------------------
	 */
	public function execute($str) {
		$this->initConnect ( true );
		if (! $this->_linkID)
			return false;
		$this->queryStr = $str;
		//释放前次的查询结果
		if ($this->queryID)
			$this->free ();
		N ( 'db_write', 1 );
		// 记录开始执行时间
		G ( 'queryStartTime' );
		$result = pg_query ( $this->_linkID, $str );
		$this->debug ();
		if (false === $result) {
			$this->error ();
			return false;
		} else {
			$this->numRows = pg_affected_rows ( $result );
			$this->lastInsID = $this->last_insert_id ();
			return $this->numRows;
		}
	}
	
	/**
     +----------------------------------------------------------
	 * 用于获取最后插入的ID
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 * @return integer
     +----------------------------------------------------------
	 */
	public function last_insert_id() {
		$query = "SELECT LASTVAL() AS insert_id";
		$result = pg_query ( $this->_linkID, $query );
		list ( $last_insert_id ) = pg_fetch_array ( $result, null, PGSQL_ASSOC );
		pg_free_result ( $result );
		return $last_insert_id;
	}
	
	/**
     +----------------------------------------------------------
	 * 启动事务
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 * @return void
     +----------------------------------------------------------
	 * @throws ThinkExecption
     +----------------------------------------------------------
	 */
	public function startTrans() {
		$this->initConnect ( true );
		if (! $this->_linkID)
			return false;
		
		//数据rollback 支持
		if ($this->transTimes == 0) {
			pg_exec ( $this->_linkID, 'begin;' );
		}
		$this->transTimes ++;
		return;
	}
	
	/**
     +----------------------------------------------------------
	 * 用于非自动提交状态下面的查询提交
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 * @return boolen
     +----------------------------------------------------------
	 * @throws ThinkExecption
     +----------------------------------------------------------
	 */
	public function commit() {
		if ($this->transTimes > 0) {
			$result = pg_exec ( $this->_linkID, 'end;' );
			if (! $result) {
				throw_exception ( $this->error () );
			}
			$this->transTimes = 0;
		}
		return true;
	}
	
	/**
     +----------------------------------------------------------
	 * 事务回滚
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 * @return boolen
     +----------------------------------------------------------
	 * @throws ThinkExecption
     +----------------------------------------------------------
	 */
	public function rollback() {
		if ($this->transTimes > 0) {
			$result = pg_exec ( $this->_linkID, 'abort;' );
			if (! $result) {
				throw_exception ( $this->error () );
			}
			$this->transTimes = 0;
		}
		return true;
	}
	
	/**
     +----------------------------------------------------------
	 * 获得所有的查询数据
     +----------------------------------------------------------
	 * @access private
     +----------------------------------------------------------
	 * @return array
     +----------------------------------------------------------
	 * @throws ThinkExecption
     +----------------------------------------------------------
	 */
	private function getAll() {
		//返回数据集
		$result = pg_fetch_all ( $this->queryID );
		pg_result_seek ( $this->queryID, 0 );
		return $result;
	}
	
	/**
     +----------------------------------------------------------
	 * 取得数据表的字段信息
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 * @throws ThinkExecption
     +----------------------------------------------------------
	 */
	public function getFields($tableName) {
		$result = $this->query ( "select a.attname as \"Field\",
            t.typname as \"Type\",
            a.attnotnull as \"Null\",
            i.indisprimary as \"Key\",
            d.adsrc as \"Default\"
            from pg_class c
            inner join pg_attribute a on a.attrelid = c.oid
            inner join pg_type t on a.atttypid = t.oid
            left join pg_attrdef d on a.attrelid=d.adrelid and d.adnum=a.attnum
            left join pg_index i on a.attnum=ANY(i.indkey) and c.oid = i.indrelid
            where (c.relname='{$tableName}' or c.relname = lower('{$tableName}'))   AND a.attnum > 0
                order by a.attnum asc;" );
		$info = array ();
		if ($result) {
			foreach ( $result as $key => $val ) {
				$info [$val ['Field']] = array ('name' => $val ['Field'], 'type' => $val ['Type'], 'notnull' => ( bool ) ($val ['Null'] == 't' ? 1 : 0), // 't' is 'not null'
'default' => $val ['Default'], 'primary' => (strtolower ( $val ['Key'] ) == 't'), 'autoinc' => (strtolower ( $val ['Default'] ) == "nextval('{$tableName}_id_seq'::regclass)") );
			}
		}
		return $info;
	}
	
	/**
     +----------------------------------------------------------
	 * 取得数据库的表信息
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 * @throws ThinkExecption
     +----------------------------------------------------------
	 */
	public function getTables($dbName = '') {
		$result = $this->query ( "select tablename as Tables_in_test from pg_tables where  schemaname ='public'" );
		$info = array ();
		foreach ( $result as $key => $val ) {
			$info [$key] = current ( $val );
		}
		return $info;
	}
	/**
     +----------------------------------------------------------
	 * 关闭数据库
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 */
	public function close() {
		if ($this->_linkID) {
			pg_close ( $this->_linkID );
		}
		$this->_linkID = null;
	}
	
	/**
     +----------------------------------------------------------
	 * 数据库错误信息
	 * 并显示当前的SQL语句
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 * @return string
     +----------------------------------------------------------
	 * @throws ThinkExecption
     +----------------------------------------------------------
	 */
	public function error($result = true) {
		$this->error = $result ? pg_result_error ( $this->queryID ) : pg_last_error ( $this->_linkID );
		if ($this->debug && '' != $this->queryStr) {
			$this->error .= "\n [ SQL语句 ] : " . $this->queryStr;
		}
		return $this->error;
	}
	
	/**
     +----------------------------------------------------------
	 * SQL指令安全过滤
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 * @param string $str  SQL指令
     +----------------------------------------------------------
	 * @return string
     +----------------------------------------------------------
	 * @throws ThinkExecption
     +----------------------------------------------------------
	 */
	public function escapeString($str) {
		return pg_escape_string ( $str );
	}
	
	/**
     +----------------------------------------------------------
	 * limit
     +----------------------------------------------------------
	 * @access public
     +----------------------------------------------------------
	 * @return string
     +----------------------------------------------------------
	 */
	public function parseLimit($limit) {
		$limitStr = '';
		if (! empty ( $limit )) {
			$limit = explode ( ',', $limit );
			if (count ( $limit ) > 1) {
				$limitStr .= ' LIMIT ' . $limit [1] . ' OFFSET ' . $limit [0] . ' ';
			} else {
				$limitStr .= ' LIMIT ' . $limit [0] . ' ';
			}
		}
		return $limitStr;
	}

}